<?php
//database connection info
$dbserver = "localhost";
$dbuser = "root";
$dbpass = "";
$db = "rtbray";

$book_not_found = "images/books/not_found.jpg";

$_book_search_page_rows = 15;
$cover_image_location ="images/books/"; 

//queries w/o parameters
$books_query = "SELECT media_id, title, authors, cover_image FROM book";
$memberships_query = "SELECT membership_type_id, name FROM membership_type";


//queries w/ paramaters

/*book quereys*/
$book_query = "SELECT media_id, title, isbn, authors, number_of_pages, publishing_year, publisher, edition, description, location, format, cover_image, dewey_decimal_number FROM book WHERE media_id = '?'";
$get_book_exists = "SELECT media_id FROM book where media_id = '?'";
$get_book_info = "SELECT title, cover_image FROM book where media_id = '?'";
$get_book_exists_by_isbn = "SELECT media_id FROM book where isbn = '?'";
$update_book = "update book set title = '?', edition = '?', isbn = '?', authors = '?', publishing_year = '?', publisher = '?', number_of_pages = '?', 
description = '?', location = '?', format = '?', cover_image = '?', dewey_decimal_number = '?' 
where media_id = '?'";

/*Media type quereys*/
$all_media_types= "SELECT * FROM media_type";
$add_media_type= "insert into media_type (name, description, checkout_duration, renewal_limit, late_fee, fee_accumulation_time ) VALUES 
('?', '?', '?', '?', '?', '?')";
$edit_media_type= "update media_type set name = '?', description = '?', checkout_duration = '?', renewal_limit = '?', late_fee = '?', fee_accumulation_time = '?'
where media_type_id = '?'";

/*inventory quereys*/
$get_inventory_exists = "SELECT inventory_id FROM inventory_item where inventory_id = '?'";
$get_inventory = "SELECT * FROM inventory_item where inventory_id = '?'";
$get_inventory_by_media = "SELECT * FROM inventory_item where media_id = '?'";
$add_inventory = "insert into inventory_item (cost, media_id, media_type_id) VALUES 
('?', '?', '?');";
$update_inventory = "update inventory_item set cost = '?', media_id = '?', media_type_id='?' 
where inventory_id = '?';";

$admin_login_query = "SELECT name, employee_id as id FROM employee WHERE email = '?' AND password = '?'";
$user_login_query = "SELECT name, patron_id as id FROM patron WHERE email = '?' AND password = '?'";
$book_info_query = "SELECT m.name as name, i.checked_out as checked_out FROM book as b, inventory_item as i, media_type as m WHERE b.media_id = i.media_id AND i.media_type_id = m.media_type_id AND b.media_id = '?'";
$book_keyword_search = "SELECT media_id, title, authors, cover_image, description FROM book WHERE ";
$book_keyword_search_count = "SELECT COUNT(media_id) AS row_count FROM book WHERE ";
$book_keyword_search_params = "(title LIKE '%?%' OR authors LIKE '%?%' OR description LIKE '%?%')";
$user_checked_out_query = "SELECT h.inventory_id, b.title, m.name, h.renewal_count, m.renewal_limit, h.date_checked_out, m.checkout_duration FROM history_entry as h, book as b, inventory_item as i, media_type as m WHERE h.inventory_id = i.inventory_id AND i.media_id = b.media_id AND i.media_type_id = m.media_type_id AND h.checked_in_by IS NULL AND h.patron_id = '?'";

$add_patron = "INSERT INTO patron (name, email, password, phone_number, city, street, province, postal_code, birth_date, dues, created_by, membership_type_id) VALUES ('?', '?', '?', '?', '?', '?', '?', '?', '?', '?', '?', '?')";
$add_employee = "INSERT INTO employee (name, email, password, privledge) VALUES ('?', '?', '?', '?')";
$add_book = "insert into book (title, edition, isbn, authors, publishing_year, publisher, number_of_pages, description, location, format, cover_image, dewey_decimal_number) VALUES
('?', '?', '?', '?', '?', '?', '?', '?', '?', '?', '?', '?')";

$book_renewal_limit = "SELECT h.renewal_count, m.renewal_limit FROM history_entry AS h, media_type AS m, inventory_item AS i WHERE h.inventory_id = i.inventory_id AND i.media_type_id = m.media_type_id AND checked_in_by IS NULL AND i.inventory_id = '?'";
$renew_book = "UPDATE history_entry SET renewal_count = renewal_count + 1 WHERE checked_in_by IS NULL AND inventory_id = '?'";

$books_checked_out = "SELECT COUNT(checked_out_by) as count FROM history_entry WHERE checked_out_by = '?' GROUP BY checked_out_by";
$books_checked_in = "SELECT COUNT(checked_in_by) as count FROM history_entry WHERE checked_in_by = '?' GROUP BY checked_in_by";
$patrons_signed_up = "SELECT COUNT(patron_id) as count FROM patron WHERE created_by = '?'";

$patron_information = "SELECT email, phone_number, street, city, province, postal_code, birth_date, dues FROM patron WHERE patron_id = '?'";

$is_checked_out = "SELECT inventory_id FROM inventory_item WHERE checked_out = 1 AND inventory_id = '?'";
$checkout_history = "INSERT INTO history_entry (patron_id, checked_out_by, inventory_id) VALUES ('?', '?', '?')";
$checkout_inventory = "UPDATE inventory_item SET checked_out = 1 WHERE inventory_id = '?'";

$is_checked_in = "SELECT inventory_id FROM inventory_item WHERE checked_out = 0 AND inventory_id = '?'";
$checkin_date = "SELECT MAX(date_checked_out) FROM history_entry WHERE inventory_id = '?'";
$checkin_history = "UPDATE history_entry SET date_checked_in = CURRENT_TIMESTAMP, checked_in_by = '?' WHERE inventory_id = '?' AND date_checked_out = '?'";
$checkin_inventory = "UPDATE inventory_item SET checked_out = 0 WHERE inventory_id = '?'";

//used to split string by spaces, and add query parameters.
function split_query_replace($q, $qp, $params)
{
	$param_array = explode ( " " , $params  );
	$result = $q;
	
	$i = 0;
		
	foreach ($param_array as $s)
	{
		if($i == 0){
			$result = $result . preg_replace("/\?/", $s, $qp);
		}
		else{
			$result = $result . 'OR' . preg_replace("/\?/", $s, $qp);
		} 
		$i++;
	}
	
	return $result;
}


//used to add in data of queries w/ parameters
function fquery($q, $val)
{
	$result = $q;
	foreach ($val as $s)
	{
		$result = preg_replace("/\?/", $s, $result, 1);
	}
	return $result;
}

function add_limit($q, $f, $l)
{

return $q . ' LIMIT ' . $f . ', ' . $l;

}

function add_order($q, $f, $l)
{

return $q . ' ORDER BY ' . $f . ' ' . $l;
}


?>

